package com.jiuqi.bi.irp.inforesource.manager;

import com.jiuqi.bi.irp.core.common.IRPException;
import com.jiuqi.bi.irp.core.enums.BooleanEnum;
import com.jiuqi.bi.irp.core.item.DBDataStructure;
import com.jiuqi.bi.irp.inforesource.item.IResStructureImportPKValidInfoItem;
import com.jiuqi.bi.util.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.json.JSONArray;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

@Service
public class ExcelOptionalManager {

    @Autowired
    private IResStructureDataValueVaildator valueVaildator;

    private static final Logger logger = LoggerFactory.getLogger(ExcelOptionalManager.class);

    /**
     * 创建行
     *
     * @param sheet     XSSFSheet
     * @param cellStyle XSSFCellStyle
     * @param text      当前行数据
     */
    void createRow(XSSFSheet sheet, XSSFCellStyle cellStyle, String[] text) {
        XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
        for (int i = 0; i < text.length; i++) {
            createCell(row, cellStyle, i, text[i]);
        }
    }

    /**
     * 创建表单
     *
     * @param workbook XSSFWorkbook
     * @param rownum   int
     * @param text     数据
     * @return XSSFSheet
     */
    XSSFSheet createSheet(XSSFWorkbook workbook, int rownum, String[] text) {
        XSSFCellStyle headstyle = workbook.createCellStyle();
        headstyle.setAlignment(HorizontalAlignment.LEFT);

        XSSFSheet sheet = workbook.createSheet(String.format("第%s页", rownum + 1));
        XSSFRow title = sheet.createRow(0);

        for (int i = 0; i < text.length; i++) {
            createCell(title, headstyle, i, text[i]);
        }
        return sheet;
    }

    /**
     * 创建单元格
     *
     * @param row   行
     * @param style 样式
     * @param index 索引
     * @param text  文本
     */
    protected void createCell(XSSFRow row, XSSFCellStyle style, int index, String text) {
        XSSFCell cell = row.createCell(index);
        cell.setCellValue(text);
        cell.setCellStyle(style);
    }

    /**
     * 导入Excel文件
     *
     * @param workbook XSSFWorkbook
     * @return excel数据
     * @throws Exception 异常
     */
    JSONArray importExcelData(Workbook workbook, Map<String, DBDataStructure> datastructureAttributeDictronary, Map<String, DBDataStructure> databaseAttributeDictronary) throws Exception {
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet.getPhysicalNumberOfRows() <= 0) {
            logger.error("文件内容为空，不能导入");
            throw new Exception("文件内容为空，不能导入");
        }

        Row firstRow = sheet.getRow(0);
        List<String> fieldNamesToProcess = new ArrayList<>();
        List<String> fieldNamesToShow = new ArrayList<>();
        ArrayList<DBDataStructure> datastructureAttributes = new ArrayList<>(datastructureAttributeDictronary.values());
        //抽取表头
        extractNeededHeaderInfo(firstRow, datastructureAttributes, fieldNamesToProcess, fieldNamesToShow);
        //校验表头
        validHeader(fieldNamesToProcess, datastructureAttributes);

        //处理字段值
        StringBuffer verifyMes = new StringBuffer();
        JSONArray result = new JSONArray();
        Set<IResStructureImportPKValidInfoItem> databasePrimaryKeySet = new HashSet<>();
        Set<IResStructureImportPKValidInfoItem> dataStructurePrimaryKeySet = new HashSet<>();
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            //当前行不存在 、 整行都没有值  跳过当前行
            if (row == null || checkRowNullXSSF(row)) {
                continue;
            }

            //获取当前行的数据
            JSONObject cruRowData = new JSONObject();
            //当前行主键
            IResStructureImportPKValidInfoItem databasePKValidInfoItem = new IResStructureImportPKValidInfoItem();
            IResStructureImportPKValidInfoItem dataStructurePKValidInfoItem = new IResStructureImportPKValidInfoItem();
            for (int idx = 0; idx < firstRow.getLastCellNum(); idx++) {
                Cell cell = row.getCell(idx);
                //注意此处不能设置条件当 cell为null时 continue，因为需要生成非空报错信息
                //单元格下标超过需处理个数   跳过当前单元格
                if (idx >= fieldNamesToProcess.size())
                    continue;

                //获取数据
                String filedName = fieldNamesToProcess.get(idx);
                DBDataStructure dataStructureAttribute = datastructureAttributeDictronary.get(filedName);
                DBDataStructure databaseAttribute = databaseAttributeDictronary.get(filedName);
                Object value = new Object();
                //当前校验类型
                int curValidType = 0;
                try {

                    //依据 数据库 表结构属性项 进行校验
                    value = valueVaildator.getAndValidCell(cell, dataStructureAttribute, databaseAttribute);
                    Integer databaseIsPrimaryKey = databaseAttribute.getIsPrimaryKey(); //是否主键
                    if (databaseIsPrimaryKey == BooleanEnum.YES.id()) {
                        if (cell == null || StringUtils.isEmpty(value.toString().trim()))
                            throw new Exception("键属性【" + fieldNamesToShow.get(idx) + "】不能为空！");
                        else
                            databasePKValidInfoItem.add(idx, fieldNamesToShow.get(idx), value);
                    }


                    //依据 数据结构 表结构属性项 进行校验
                    curValidType = 1;
                    value = valueVaildator.getAndValidCell(cell, dataStructureAttribute, dataStructureAttribute);
                    Integer dataStructureIsPrimaryKey = dataStructureAttribute.getIsPrimaryKey(); //是否主键
                    if (dataStructureIsPrimaryKey == BooleanEnum.YES.id()) {
                        if (value == null || StringUtils.isEmpty(value.toString().trim()))
                            throw new Exception("键属性【" + fieldNamesToShow.get(idx) + "】不能为空！");
                        else
                            dataStructurePKValidInfoItem.add(idx, fieldNamesToShow.get(idx), value);
                    }

                } catch (Exception e) {
                    buildImportMessage(i, idx, verifyMes, fieldNamesToShow.get(idx), e.getMessage(), curValidType);
                }
                cruRowData.put(fieldNamesToProcess.get(idx), value);
            }

            boolean isDatabasePKRepeat = false;
            if (!databasePKValidInfoItem.isEmpty()
                    &&
                    !databasePrimaryKeySet.add(databasePKValidInfoItem)) {
                List<Integer> columnIndexList = databasePKValidInfoItem.getColumnIndexList();
                List<String> columnTitleList = databasePKValidInfoItem.getColumnTitleList();
                for (int j = 0; j < columnIndexList.size(); j++) {
                    buildImportMessage(i, columnIndexList.get(j), verifyMes, columnTitleList.get(j), "主键值重复", 0);
                }
                isDatabasePKRepeat = true;
            }
            if (!isDatabasePKRepeat
                    &&
                    !dataStructurePKValidInfoItem.isEmpty()
                    &&
                    !dataStructurePrimaryKeySet.add(dataStructurePKValidInfoItem)) {
                List<Integer> columnIndexList = dataStructurePKValidInfoItem.getColumnIndexList();
                List<String> columnTitleList = dataStructurePKValidInfoItem.getColumnTitleList();
                for (int j = 0; j < columnIndexList.size(); j++) {
                    buildImportMessage(i, columnIndexList.get(j), verifyMes, columnTitleList.get(j), "主键值重复", 1);
                }
            }

            result.put(cruRowData);
        }
        //校验不通过，直接抛出异常
        if (StringUtils.isNotEmpty(verifyMes.toString())) {
            throw new IRPException(verifyMes.toString());
        }
        return result;
    }

    /**
     * 校验当前文件 与 数据结构字段属性 对照后是否存在缺失情况
     *
     * @param fieldNamesToProcess
     * @param datastructureAttributes
     * @throws Exception
     */
    private void validHeader(List<String> fieldNamesToProcess, List<DBDataStructure> datastructureAttributes) throws Exception {
        StringBuffer missTitlebuffer = new StringBuffer();
        //注意：extractNeededHeaderInfo操作后，只存在 小于 、 等于 两种情况
        if (fieldNamesToProcess.size() < datastructureAttributes.size()) {
            List<String> missedFieldNamesToShow =
                    datastructureAttributes
                            .stream()
                            .filter(item -> !fieldNamesToProcess.contains(item.getFieldName().toUpperCase())
                                    &&
                                    !fieldNamesToProcess.contains(item.getFieldName().toLowerCase()))
                            .map(o -> ("【" + o.getFieldTitle() + "[" + o.getFieldName() + "]】"))
                            .collect(Collectors.toList());
            if (!missedFieldNamesToShow.isEmpty()) {
                missTitlebuffer.append("导入文件缺失表头");

                String missCondition = String.join(",", missedFieldNamesToShow);
                missCondition += ";";

                missTitlebuffer.append(missCondition);
            }
        }
        if (StringUtils.isNotEmpty(missTitlebuffer.toString())) {
            throw new Exception(missTitlebuffer.toString());
        }
    }


    /**
     * 【注意】文件字段多于 数据结构字段属性个数 允许导入，忽略未匹配字段
     *
     * @param firstRow
     * @param datastructureAttributes
     * @param fieldNamesToProcess
     * @param fieldNamesToShow
     */
    private void extractNeededHeaderInfo(Row firstRow, List<DBDataStructure> datastructureAttributes, List<String> fieldNamesToProcess, List<String> fieldNamesToShow) {
        for (int i = 0; i < firstRow.getLastCellNum(); i++) {
            Cell rowCell = firstRow.getCell(i);
            if (rowCell == null) continue;
            String cellValue = rowCell.getStringCellValue();
            Optional<DBDataStructure> optional =
                    datastructureAttributes
                            .stream()
                            .filter(item -> StringUtils.isNotEmpty(item.getFieldName()))
                            .filter(item -> (item.getFieldTitle() + "[" + item.getFieldName() + "]").equalsIgnoreCase(cellValue))
                            .findFirst();
            if (optional.isPresent()) {
                DBDataStructure datastructureAttribute = optional.get();
                fieldNamesToProcess.add(datastructureAttribute.getFieldName());
                fieldNamesToShow.add(datastructureAttribute.getFieldTitle() + "[" + datastructureAttribute.getFieldName() + "]");
            }
        }
    }

    /**
     * 构建导入文件的校验信息
     *
     * @param i         当前行
     * @param idx       当前列
     * @param verifyMes 校验结果
     * @param title     当前列名称
     * @param message   校验信息
     */
    private void buildImportMessage(int i, int idx, StringBuffer verifyMes, String title, String message, int curValidType) {
        if (curValidType == 0)
            verifyMes.append("【数据库校验】：");
        if (curValidType == 1)
            verifyMes.append("【数据结构校验】：");

        verifyMes.append("导入文件第").append(i + 1).append("行");
        verifyMes.append("第").append(idx + 1).append("列");
        verifyMes.append("【").append(title).append("】列：");
        verifyMes.append(message).append(" \r\n");
    }

    private boolean checkRowNullXSSF(Row row) {
        Iterator<Cell> cellItr = row.cellIterator();

        while (cellItr.hasNext()) {
            Cell c = cellItr.next();
            if (c.getCellType() != CellType.BLANK) {
                try {
                    if (StringUtils.isNotEmpty(c.getStringCellValue().trim())) {
                        return false;
                    }
                } catch (Exception e) {
                    return false;
                }

            }
        }
        return true;
    }
}
